package com.wxg.sqlite;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

import com.wxg.sqlite.util.Sql;

public class EnglishWordDB {
	
	// pattern
	private static final String PATTERN = "yyyy-MM-dd HH:mm:ss.SSS";
	
	public static final SimpleDateFormat SDF = new SimpleDateFormat(PATTERN);

	public long addEnglishWord(Map<String,String> param){
		long id = 0;
		int result = -1;
		Connection conn = null;
		String addStr = null;
		String queryStr = null;
		{
			String timeStr = SDF.format(new java.util.Date());
			Sql sql = new Sql();
			sql.add(" insert into ").heel(EnglishWordDbInitialize.TB_ENGLISHWORD);
			sql.add(" values(");
			sql.add("null").heel(",");
			sql.quotation(param.get("word")).heel(",");
			sql.empty(param.get("usa_path")).heel(",");
			sql.empty(param.get("britain_path")).heel(",");
			sql.quotation( timeStr ).heel(",");
			sql.quotation( timeStr ).heel(")");
			addStr = sql.str();
			sql.print();
		}
		{
			Sql sql = new Sql();
			sql.add(" select max(id) from ").heel(EnglishWordDbInitialize.TB_ENGLISHWORD);
			sql.add(" where word = '");
			sql.heel( param.get("word") ).heel("'");
			queryStr = sql.str();
		}
		try {
			conn = SqliteUtils.getConnection();
			Statement stmt = conn.createStatement();
			result = stmt.executeUpdate(addStr);
			if( result != -1 ){
				ResultSet rs = stmt.executeQuery(queryStr);
				while(rs.next()){
					id = rs.getLong(1);
				}
			}
			SqliteUtils.closeConnection(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	
	public int delEnglishWordByWord(String word){
		int result = -1;
		String delStr = null;
		{
			Sql sql = new Sql();
			sql.add(" delete from ").heel(EnglishWordDbInitialize.TB_ENGLISHWORD);
			sql.add(" where word = ");
			sql.quotation(word);
			sql.print();
			delStr = sql.str();
		}
		try {
			Connection conn = SqliteUtils.getConnection();
			Statement stmt = conn.createStatement();
			result = stmt.executeUpdate(delStr);
			SqliteUtils.closeConnection(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	
	public static void main(String[] args) {
		EnglishWordDB db = new EnglishWordDB();
		Map<String,String> param = new HashMap<String,String>();
		param.put("word", "book");
		param.put("usa_path", "");
		param.put("britain_path", "");
		long id = db.addEnglishWord(param);
		System.out.println( String.format("id is %d", id));
		System.out.println( String.format(" delete %s datas!", db.delEnglishWordByWord("book")) );
	}
	
}
